Composite Indexes and Column Order in MySQL

Course: MySQL Training Course

Topic: Composite Indexes and Column Order

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Composite Indexes

A composite index in MySQL is an index created on two or more columns of a table. It helps MySQL locate records efficiently when queries filter, sort, or search using combinations of those columns.

For example, an e-commerce application may frequently retrieve orders belonging to a particular customer and arrange those orders by date. Instead of creating unrelated indexes for every query, a composite index on customer_id and order_date may support this access pattern.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This index contains two columns in a specific order: customer_id first and order_date second. That order is important because MySQL organizes the index using the first column, followed by the subsequent columns within matching values of the preceding columns.

Composite indexes are also called multi-column indexes or compound indexes. They can be primary-key indexes, unique indexes, or non-unique secondary indexes, depending on how they are defined.

2. Learning Objectives

After studying this topic, you will be able to:

• Define composite indexes and explain their purpose.
• Create composite indexes using MySQL SQL statements.
• Understand why column order affects index performance.
• Apply the leftmost prefix principle.
• Choose index columns based on query conditions.
• Understand the relationship between equality filters, range filters, and sorting.
• Use composite indexes with WHERE, ORDER BY, and JOIN operations.
• Inspect index usage with EXPLAIN.
• Identify redundant indexes and common indexing mistakes.
• Design and test indexes for practical database applications.

3. What Is a Composite Index?

A composite index stores index entries based on the values of multiple columns. MySQL orders these entries according to the indexed columns in the order specified when the index is created.

Consider an employees table containing the following columns:

If the application frequently searches for employees in a specific department and city, a composite index may be useful.

CREATE INDEX idx_employees_department_city
ON employees(department, city);

This index can help queries that filter by department alone or by department and city together. It is not generally equivalent to an index that begins with city.

3.1 Composite Index vs Single-Column Indexes

Suppose you create these indexes:

CREATE INDEX idx_employees_department
ON employees(department);

CREATE INDEX idx_employees_city
ON employees(city);

These indexes are separate structures. MySQL may use one index, or in certain cases combine indexes through an optimization such as Index Merge. However, separate indexes are not always as effective as a composite index designed for a frequent combination of conditions.

For example, the composite index below may be more suitable for queries that regularly filter by both department and city:

CREATE INDEX idx_employees_department_city
ON employees(department, city);

The best choice depends on the actual queries, data distribution, storage overhead, and execution plans.

4. Creating Composite Indexes in MySQL

4.1 Using CREATE INDEX

The CREATE INDEX statement creates an index on multiple columns. The order in the parentheses determines the index column order.

CREATE INDEX index_name
ON table_name(column1, column2, column3);

Example:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

This index starts with customer_id and then uses status to distinguish entries within each customer_id value.

4.2 Creating a Composite Index During Table Creation

You can define an index directly in a CREATE TABLE statement.

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    INDEX idx_customer_status (customer_id, status),
    INDEX idx_status_date (status, order_date)
);

The table has two composite secondary indexes:

• idx_customer_status: customer_id is first, followed by status.
• idx_status_date: status is first, followed by order_date.

These indexes support different access patterns. The first is suited to queries that begin with customer_id, while the second is suited to queries that begin with status.

4.3 Creating a Composite Unique Index

A composite unique index enforces uniqueness on the combination of its indexed columns.

CREATE TABLE course_enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    UNIQUE KEY uq_student_course (student_id, course_id)
);

This prevents the same student_id and course_id combination from appearing more than once. A student may enroll in different courses, and a course may have different students, but the same pair cannot be duplicated.

5. Understanding Column Order

Column order is one of the most important considerations when creating a composite index. An index on (A, B) is not the same as an index on (B, A).

For example:

CREATE INDEX idx_department_city
ON employees(department, city);

CREATE INDEX idx_city_department
ON employees(city, department);

The first index begins with department. The second begins with city. They may support overlapping queries, but their usefulness differs depending on which columns the query uses.

5.1 Index on (department, city)

This index is generally suitable for queries such as:

SELECT *
FROM employees
WHERE department = 'IT';

SELECT *
FROM employees
WHERE department = 'IT'
  AND city = 'Mumbai';

It may also support appropriate ordering by city within a fixed department.

5.2 Index on (city, department)

This index is generally suitable for queries such as:

SELECT *
FROM employees
WHERE city = 'Mumbai';

SELECT *
FROM employees
WHERE city = 'Mumbai'
  AND department = 'IT';

Both queries use the leading city column. The second query also filters by department.

5.3 Why the Two Orders Are Different

In an index on (department, city), entries are organized first by department and then by city within each department. In an index on (city, department), entries are organized first by city and then by department within each city.

Consequently, MySQL can usually navigate directly to the relevant section when a query filters on the first indexed column. If a query only filters on the second column, the index often cannot provide the same efficient lookup.

6. The Leftmost Prefix Principle

The leftmost prefix principle explains how MySQL can use a composite index beginning with one or more of its leading columns.

Suppose the index is:

CREATE INDEX idx_orders_customer_status_date
ON orders(customer_id, status, order_date);

The indexed columns are ordered as follows:

1. customer_id
2. status
3. order_date

MySQL can generally use this index for searches beginning with customer_id, including suitable combinations of customer_id, status, and order_date.

Important: "Partial" does not mean the index is useless. MySQL may use a leading portion of the index, apply remaining conditions as filters, use other optimizations, or choose another plan. Always check EXPLAIN for the actual query.

7. Equality Conditions and Composite Indexes

Equality conditions compare a column to a specific value, commonly using the equals operator. They often work well with composite indexes when the conditions match the leading columns.

SELECT order_id, customer_id, status
FROM orders
WHERE customer_id = 20
  AND status = 'Shipped';

A composite index on (customer_id, status) is a natural candidate for this query because both leading columns have equality conditions.

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

When designing an index for several equality predicates, the relative order of those equality columns may be less important for that specific query if both are always constrained. However, their order still matters for other queries that use only one of the columns, for range conditions, and for ordering.

8. Range Conditions and Column Order

Range conditions retrieve values within a range, such as greater than, less than, BETWEEN, or a date range. With composite indexes, a range condition can affect how efficiently later index columns are used for narrowing the search.

8.1 Example of a Range Condition

SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 20
  AND order_date >= '2026-01-01';

An index on (customer_id, order_date) may support this query effectively because the equality condition on customer_id is followed by a range condition on order_date.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

8.2 Range Condition Before Another Indexed Column

Consider this index:

CREATE INDEX idx_orders_date_status
ON orders(order_date, status);

For a query that filters on an order_date range and status, MySQL can use the date range to navigate the index, but status may not narrow the scanned index range in the same way it would if status were a leading equality column. MySQL may still evaluate status through index filtering or another applicable optimization.

SELECT order_id, order_date, status
FROM orders
WHERE order_date >= '2026-01-01'
  AND status = 'Shipped';

Do not apply a rigid rule that every range column must always be last. Instead, identify the queries that matter most, then evaluate whether equality-first, range-first, or another column order provides the best overall workload performance.

9. Composite Indexes With ORDER BY

MySQL may use an index to retrieve rows in a required order, reducing or avoiding a separate sorting step. This depends on the index column order, filtering conditions, requested sort direction, and query structure.

9.1 Example: Orders by Customer and Date

SELECT order_id, order_date
FROM orders
WHERE customer_id = 20
ORDER BY order_date DESC;

A composite index on (customer_id, order_date) is a candidate because customer_id is filtered to a single value and the results are ordered by order_date.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

9.2 Multiple Sorting Columns

Suppose a report filters orders by status and sorts by date and amount:

SELECT order_id, order_date, total_amount
FROM orders
WHERE status = 'Shipped'
ORDER BY order_date, total_amount;

An index on (status, order_date, total_amount) may help the filtering and ordering pattern, depending on the data and plan.

CREATE INDEX idx_orders_status_date_amount
ON orders(status, order_date, total_amount);

Indexes with mixed ASC and DESC sort requirements need special attention. MySQL version, index definition, and query direction affect whether an index can satisfy the ordering efficiently. Validate the plan rather than assuming a sort will always be avoided.

10. Composite Indexes With JOIN Operations

Composite indexes can help when a query joins tables and filters using more than one column. The index should reflect how the query accesses each table.

10.1 Example Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    INDEX idx_orders_customer_status (customer_id, status)
);

10.2 Query Using JOIN

SELECT c.customer_name, o.order_id, o.status
FROM customers AS c
JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE c.customer_id = 10
  AND o.status = 'Shipped';

The primary key on customers.customer_id supports locating the customer. The composite index on orders(customer_id, status) may help locate matching orders for that customer and status.

For other workloads that primarily search orders by status alone, an index beginning with status may be more useful. Choose indexes based on actual access patterns and inspect the query plan.

11. Composite Indexes and Covering Queries

A covering index contains all the columns needed by a particular query, allowing MySQL to retrieve the required information from the index without fetching the complete row from the clustered index. This can reduce additional row lookups.

11.1 Example

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Consider:

SELECT customer_id, order_date
FROM orders
WHERE customer_id = 20
ORDER BY order_date;

The index contains both selected columns and may cover this query. If the query also selects total_amount, that column is not explicitly in the index, although InnoDB secondary-index entries contain the primary-key columns. MySQL may need to retrieve the row or use another suitable index to obtain total_amount.

Adding every selected column to an index is not automatically a good idea. Wider indexes consume more storage and increase the cost of writes. Add covering columns only when the measured performance benefit justifies the overhead.

12. Composite Indexes and Selectivity

Selectivity describes how effectively a column distinguishes rows. A column with many distinct values is often more selective than a column with only a few possible values.

For example, customer_id may identify a particular customer's orders, while status might contain only values such as Pending, Shipped, Cancelled, and Delivered.

Consider two indexes:

CREATE INDEX idx_customer_status
ON orders(customer_id, status);

CREATE INDEX idx_status_customer
ON orders(status, customer_id);

If queries commonly search orders for one customer, an index beginning with customer_id may be the better choice. If queries commonly filter all orders by status and then by customer, the alternative order may be appropriate.

However, selectivity alone does not determine the ideal order. Common query patterns, sorting, range conditions, data distribution, and the total mix of reads and writes must also be considered.

13. How to Choose the Correct Column Order

Use the following practical process when designing a composite index.

1. Identify frequent queries: Review the WHERE, JOIN, ORDER BY, and GROUP BY clauses of important queries.
2. Identify common leading filters: Determine which columns are commonly used to locate the relevant records.
3. Consider equality predicates: Equality filters can often form the leading part of a composite index.
4. Consider range predicates: Check how range conditions affect the use of subsequent columns.
5. Consider ordering requirements: Include sort columns in a useful sequence when this supports important queries.
6. Review selectivity and distribution: Estimate how many rows each predicate is likely to match.
7. Check existing indexes: Avoid unnecessary duplicates and overlapping indexes without a clear benefit.
8. Test with EXPLAIN: Inspect the execution plan for the actual query.
9. Measure performance: Compare query time, rows examined, and write overhead using representative data.
10. Review the complete workload: Optimize for the overall application rather than a single query in isolation.

14. Using EXPLAIN to Analyze Composite Indexes

EXPLAIN shows the execution plan selected by MySQL. It helps determine which indexes are considered and whether the optimizer expects to use an index for a particular query.

14.1 Example Table and Index

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    INDEX idx_customer_status_date
        (customer_id, status, order_date)
);

14.2 Query Using All Leading Columns

EXPLAIN
SELECT order_id, order_date
FROM orders
WHERE customer_id = 20
  AND status = 'Shipped'
  AND order_date >= '2026-01-01';

The composite index is a candidate because the query constrains the leading columns and includes a date range. Inspect the plan to confirm what MySQL actually chooses.

14.3 Query Using Only the Second Column

EXPLAIN
SELECT order_id, status
FROM orders
WHERE status = 'Shipped';

The index begins with customer_id, so it is generally not an efficient direct lookup index for status alone. Depending on the data and query, MySQL may choose a table scan or another access strategy.

14.4 Important EXPLAIN Fields

A table scan is not necessarily inefficient. If a query needs a large percentage of a small table, reading the table directly may be cheaper than using an index. Use EXPLAIN and actual measurements to understand the choice.

15. Common Mistakes With Composite Indexes

15.1 Choosing Column Order Without Reviewing Queries

Creating an index based only on the order in which columns appear in a table can lead to poor results. Index order should be chosen based on important query patterns.

15.2 Assuming Both Column Orders Are Equivalent

An index on (customer_id, order_date) is not interchangeable with an index on (order_date, customer_id). Their leading columns support different lookups.

15.3 Ignoring the Leftmost Prefix Principle

A composite index on (department, city) may support queries beginning with department, but is generally not as effective for city-only lookups. If city-only queries are important, evaluate an index that begins with city.

15.4 Creating Many Overlapping Indexes

Adding indexes for every query can increase disk usage and slow down INSERT, UPDATE, and DELETE operations. Check existing indexes and verify the benefit of each proposed addition.

15.5 Making Indexes Wider Than Necessary

Adding many columns can increase storage and write overhead. Include additional columns only when they provide a measured advantage for important queries.

15.6 Ignoring Range Conditions

Range predicates may affect how later columns in a composite index can narrow the index search. Test realistic queries rather than relying on a universal ordering rule.

15.7 Assuming MySQL Must Use the Index

The query optimizer chooses an execution plan based on available indexes, statistics, estimated costs, and other factors. An index being available does not guarantee that it will be selected.

16. Managing Composite Indexes

16.1 View Index Definitions

SHOW INDEX FROM orders;

Review the index name and the sequence of indexed columns to confirm their order.

16.2 Add a Composite Index

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

16.3 Remove an Unneeded Index

ALTER TABLE orders
DROP INDEX idx_orders_customer_date;

Only remove an index after checking application dependencies and query performance. An index may support multiple queries, not just the query that originally motivated its creation.

16.4 Check Index Column Order

SHOW INDEX FROM orders;

Inspect the Seq_in_index information to see the position of each column within the index. Position 1 identifies the leading column.

17. Practical Project: Index Optimization for an Order Management System

Project goal: Design composite indexes for an order management system that needs to retrieve customer orders, filter orders by status, and display recent orders.

17.1 Create the Orders Table

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
);

17.2 Add an Index for Customer Order History

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

This index may help queries that filter by customer_id and order by order_date.

17.3 Add an Index for Status-Based Reporting

CREATE INDEX idx_orders_status_date
ON orders(status, order_date);

This index may help queries that filter by status and retrieve orders within a date range.

17.4 Test Customer Order History

EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 10
ORDER BY order_date DESC;

17.5 Test Status-Based Reporting

EXPLAIN
SELECT order_id, order_date
FROM orders
WHERE status = 'Shipped'
  AND order_date >= '2026-01-01'
ORDER BY order_date;

17.6 Compare the Results

Record the selected index, estimated rows, access method, and any sorting information from each plan. Repeat the tests with realistic data and measure performance. If an index is not selected, investigate the query and data distribution before adding more indexes.

18. Hands-On Exercises

Exercise 1: Create a Composite Index

1. Create an employees table containing employee_id, department, city, and salary.
2. Create a composite index on (department, city).
3. Run a query filtering only by department.
4. Run a query filtering by department and city.
5. Use EXPLAIN to inspect both plans.

Exercise 2: Compare Column Order

1. Create an index on (department, city).
2. Run a query filtering only by city.
3. Create a test environment with an index on (city, department).
4. Compare the execution plans for city-based queries.
5. Explain how the leading column affects index usage.

Exercise 3: Equality and Range Conditions

1. Create an orders table with customer_id, status, and order_date.
2. Create an index on (customer_id, order_date).
3. Test an equality condition on customer_id with a date range.
4. Test a query using only order_date.
5. Compare the plans and determine whether another index is justified.

Exercise 4: Optimize Sorting

1. Create a composite index on (customer_id, order_date).
2. Query orders for one customer sorted by date.
3. Inspect EXPLAIN for sorting-related information.
4. Compare the result with a version that has no suitable composite index.
5. Document any performance differences using representative data.

19. Interview Questions and Answers

Q1. What is a composite index in MySQL?

Answer: A composite index is an index built on two or more columns. It can improve queries that use suitable combinations of those columns.

Q2. Why is column order important in a composite index?

Answer: MySQL organizes index entries according to the specified column order. Queries that use the leading columns can generally use the index more efficiently than queries that filter only on later columns.

Q3. What is the leftmost prefix principle?

Answer: For an index on (A, B, C), MySQL can generally use the index for lookups beginning with A, including suitable combinations of A, B, and C. B-only or C-only lookups usually cannot use it as efficiently.

Q4. Is an index on (A, B) the same as an index on (B, A)?

Answer: No. They have different leading columns and therefore support different query patterns. One may be better for A-based queries, while the other may be better for B-based queries.

Q5. Should the most selective column always come first?

Answer: No. Selectivity matters, but query patterns, equality and range conditions, sorting requirements, and the full workload also determine the best order.

Q6. Can a composite index support a query that uses only its first column?

Answer: Yes. An index on (A, B) can generally support lookups on A alone.

Q7. Can a composite index efficiently support a query using only its second column?

Answer: Usually not as a direct lookup. MySQL may choose a scan or another optimization, depending on the query and available indexes.

Q8. How do range conditions affect composite indexes?

Answer: A range condition can limit how effectively subsequent columns narrow the index search. The actual behavior depends on the predicates and optimizer plan.

Q9. Can a composite index improve ORDER BY performance?

Answer: Yes. If the index order aligns with the query's filters and sort order, MySQL may use it to retrieve rows in the required order and reduce sorting work.

Q10. How can you confirm whether a composite index is used?

Answer: Use EXPLAIN and inspect the key, possible_keys, type, rows, and Extra fields.

Q11. Can a composite index be unique?

Answer: Yes. A composite unique index enforces uniqueness on the combination of its indexed columns.

Q12. Should every query have its own index?

Answer: No. Indexes consume storage and increase write overhead. Reuse suitable existing indexes where possible and add new indexes only when testing demonstrates a benefit.

20. Quick Revision Checklist

• A composite index contains two or more columns.
• Column order determines the index's leading column and influences query support.
• The leftmost prefix principle helps explain which filters can use a composite index.
• An index on (A, B) is not equivalent to an index on (B, A).
• Equality predicates, range predicates, and ORDER BY requirements should be considered together.
• Composite indexes can support some JOIN operations and covering queries.
• Wider and overlapping indexes increase storage and write costs.
• SHOW INDEX reveals the indexed columns and their sequence.
• EXPLAIN helps verify the optimizer's chosen access plan.
• Test index designs using realistic data and representative queries.

21. Conclusion

Composite indexes are valuable tools for improving MySQL query performance when applications frequently filter, join, or sort using multiple columns. Their effectiveness depends not only on which columns are indexed but also on the order in which those columns appear.

The leftmost prefix principle provides a practical starting point for understanding index usage. Equality conditions, range conditions, sorting requirements, selectivity, and existing indexes should all be considered when choosing a column order. A well-designed composite index can improve query performance, while unnecessary or poorly ordered indexes can increase storage requirements and slow down data modifications.

Use CREATE INDEX, SHOW INDEX, and EXPLAIN to create, inspect, and evaluate indexes. Confirm the benefit of each design using realistic workloads before deploying changes to production.

Continue learning: Visit the JustAcademy MySQL Training Course to explore more database topics, or register for a course demo to learn about the training program.